<?php
/**
 * @Description: 任教学科
 * @Author: kzj
 * @datetime: 2023-02-24
 */
namespace App\Services\Teaching;

use App\Http\Controllers\Excel\ExcelExport;
use App\Http\Controllers\Excel\ExcelImport;
use App\Models\ActivityInfo;
use App\Models\DictionaryItem;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Facades\Excel;

/**
 * 教育教学 (服务)
 */
class TeachingExportService
{
    /** @var \App\Repositories\System\ActivityRepository $activityRepository */
    private $activityRepository;
    /** @var \App\Repositories\System\ParticipantRepository $participantRepository */
    private $participantRepository;
    /** @var \App\Repositories\System\SystemTaskRepository $systemTaskRepository */
    private $systemTaskRepository;

    public function __construct()
    {
        $this->activityRepository = app('activityRepository');
        $this->participantRepository = app('participantRepository');
        $this->systemTaskRepository = app('systemTaskRepository');
    }

    /*
     * @Description: 公开教学导出
     * @Author:  Kzj
     * @Date: 2023-11-17 14:43:04
     */
    public function exportOpen($request)
    {
        $formKey = NULL;
        $termId = 0;
        $name = '';
        $status = '';
        $hostName = ''; //主持人
        $beginDate = '';
        $endDate = '';
        $joinName = '';
        $creatorName = '';
        $rangeDate = [];
        $jsonSearch = [];
        $hasSearch = false;

        $user = Auth::user();
        $roleType = $user->cur_role_type;
        // dd($roleType);

        if($request['form_key'] == 'TEARRESEARCH_OPEN_TABLE') {//公开教学
            // 教育工作 - 班队会：合并到公开教学；原班队会的数据移至公开教学模块中，类型为教育；原公开教学的数据，类型为教学
            if($roleType == 1) {
                $formKey = 'EW_CLASS_MEETING_TABLE';//班队会
            } else if ($roleType == 2) {
                $formKey = 'TEARRESEARCH_OPEN_TABLE';
            } else {
                $formKey = [$request['form_key'], 'EW_CLASS_MEETING_TABLE'];
            }
        }
        if (isset($request['search']['data_version_id'] )&& !empty($request['search']['data_version_id'])){
            $hasSearch = true;
            $termId = $request['search']['data_version_id'];
        }
        if (isset($request['search']['name']) && !empty($request['search']['name'])){
            $hasSearch = true;
            $name = $request['search']['name'];
        }
        if (isset($request['search']['status']) && !empty($request['search']['status'])){
            $hasSearch = true;
            $status = $request['search']['status'];
        }
        if (isset($request['search']['host_name']) && !empty($request['search']['host_name'])){
            $hasSearch = true;
            $hostName = $request['search']['host_name'];
        }
        if (isset($request['search']['begin_date']) && !empty($request['search']['begin_date'])){
            $hasSearch = true;
            $beginDate = $request['search']['begin_date'];
        }
        if (isset($request['search']['end_date']) && !empty($request['search']['end_date'])){
            $hasSearch = true;
            $endDate = $request['search']['end_date'];
        }
        //通过json的类型及optionId值进行查询
        if (isset($request['json_search']['pubications_publish_type'] )&& !empty($request['json_search']['pubications_publish_type'])){
            $hasSearch = true;
            $jsonSearch['pubications_publish_type'] = $request['json_search']['pubications_publish_type'];
        }
        if (isset($request['search']['level'] )&& !empty($request['search']['level'])){
            $hasSearch = true;
            $jsonSearch['level'] = $request['search']['level'];
        }
        if (isset($request['search']['course'] )&& !empty($request['search']['course'])){
            $hasSearch = true;
            $jsonSearch['course'] = $request['search']['course'];
        }
        if (isset($request['search']['grade'] )&& !empty($request['search']['grade'])){
            $hasSearch = true;
            $jsonSearch['grade'] = $request['search']['grade'];
        }
        if (isset($request['search']['class_item'] )&& !empty($request['search']['class_item'])){
            $hasSearch = true;
            $jsonSearch['class_item'] = $request['search']['class_item'];
        }
        if (isset($request['search']['category'] )&& !empty($request['search']['category'])){
            $hasSearch = true;
            $jsonSearch['category'] = $request['search']['category'];
        }
        if (isset($request['search']['reception_class'] )&& !empty($request['search']['reception_class'])){
            $hasSearch = true;
            $jsonSearch['reception_class'] = $request['search']['reception_class'];
        }
        if (isset($request['search']['date_range'] )&& !empty($request['search']['date_range'])){
            $hasSearch = true;
            $rangeDate = explode(' - ', $request['search']['date_range']);
        }
        if (isset($request['search']['join_name'] )&& !empty($request['search']['join_name'])){
            $hasSearch = true;
            $joinName = $request['search']['join_name'];
        }
        if (isset($request['search']['creator_name'] )&& !empty($request['search']['creator_name'])){
            $hasSearch = true;
            $creatorName = $request['search']['creator_name'];
        }
        if (!$hasSearch){
            $status = 'PASS';//导出的数据均为审核通过的
        }

        $user = Auth::user();
        $viewAll = $this->activityRepository->getViewPower($request['form_key']);

        $output = ActivityInfo::select('activity_info.*', 'b.status')
            ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
            ->when($formKey, function($q) use($formKey) {
                if(is_array($formKey)) {
                    $q->whereIn('activity_category', $formKey);
                } else {
                    $q->where('activity_category', $formKey);
                }
            })
            ->when($termId > 0, function($q) use($termId) {
                $q->whereHas('term', function ($query) use($termId) {
                    $query->where('activity_info.data_version_id', $termId);
                });
            })
            ->when($name, function($q) use($name) {
                $q->where('activity_info.name', 'like', '%' . $name . '%');
            })
            ->when($status, function($q) use($status) {
                $q->where('b.status', '=', $status);
            })
            ->when($beginDate, function($q) use($beginDate) {
                $q->where('activity_info.begin_date', '=', $beginDate);
            })
            ->when($endDate, function($q) use($endDate) {
                $q->where('activity_info.end_date', '=', $endDate);
            })
            ->when(!empty($jsonSearch), function($q) use($jsonSearch) {
                foreach($jsonSearch as $k => $id) {
                    $q->whereRaw(" JSON_EXTRACT(activity_info.`".$k."`,'$[0].optionId') = $id");
                }
            })
            ->when(!empty($rangeDate), function($q) use($rangeDate) {
                $q->where('activity_info.begin_date', '<=', $rangeDate[0])->where('activity_info.end_date', '>=', $rangeDate[1]);
            })
            ->when($hostName, function($q) use($hostName) {
                $q->whereHas('compereUser', function ($query) use($hostName) {
                    $query->where('nickname', 'like', '%' . $hostName . '%');
                });
            })
            ->when(isset($request['user_id']) && !empty($request['user_id']), function($q) use($request){//处理档案袋只查看自己的数据
                $q->whereHas('participants', function($query) use($request){
                    $query->where('user_id', $request['user_id']);
                });
            })
            ->when(!isset($request['user_id']), function($q) use($user, $viewAll){//处理非档案袋只查看自己的数据
                $q->when(!$viewAll && !$user->is_checker, function($qq) use($user){
                    $qq->whereHas('participants', function($query) use($user){
                        $query->where('user_id', $user->id);
                    });
                });
            })
            ->when($joinName, function($q) use($joinName) {
                $q->whereHas('joinUser', function ($query) use($joinName) {
                    $query->where('nickname', 'like', '%' . $joinName . '%');
                });
            })
            ->when($creatorName, function($q) use($creatorName) {
                $q->whereHas('creator', function ($query) use($creatorName) {
                    $query->where('nickname', 'like', '%' . $creatorName . '%');
                });
            })
            ->when(!empty($request['search']['materials_date']),function($q) use($request){
                return $q->whereRaw(" activity_info.data_version_id in ({$request['search']['materials_date']})");
            })
            ->with('term', 'forminstance', 'compere', 'participant', 'creator', 'instance:id,status')
            ->where('activity_info.deleted', 0)
            ->orderBy('activity_info.id', 'desc')
            ->get();
            //dd(DB::getQueryLog());
        // dd($output->toArray());

        $title = ['学年学期','课题名称','类别','日期','学科','年级','班级', '创建人', '状态'];
        $exportData = [];
        foreach($output as $k => $v){
            $status = '-';
            $disStatus = $v->instance ? $v['instance']['status'] : '';
            switch($disStatus) {
                case 'REVOKE': $status = '撤回'; break;
                case 'FILL_IN': $status = '未提交'; break;
                case 'REVIEW': $status = '待审核'; break;
                case 'REVIEW_FAIL': $status = '已驳回'; break;
                case 'PASS': $status = '审核通过'; break;
            }

            $activityType = '';
            switch($v->activity_category) {
                case 'EW_CLASS_MEETING_TABLE': $activityType = '教育'; break;
                case 'TEARRESEARCH_OPEN_TABLE': $activityType = '教学'; break;
            }

            $date = '';
            if($v->activity_category == 'EW_CLASS_MEETING_TABLE') {
                $date = $v->date ? $v->date : $v->begin_date;
            } else {
                $date = $v->begin_date;
            }

            $item = [
                'term' => $v->data_version_id ? $v->term->title : '',
                'name' => $v->name,
                'activity_type' => $activityType,
                'date' => $date,
                'course' => !empty($v->course_set) ? $v['course_set'][0]['selectValue'] : '',
                'grade' => !empty($v->grade_array) ? $v['grade_array'][0]['selectValue'] : '',
                'class' => !empty($v->class_item_set) ? $v['class_item_set'][0]['selectValue'] : '',
                'creator' => !empty($v->creator) ? $v->creator->nickname : '',
                'status' => $status
            ];

            $exportData[] = $item;
        }
        array_unshift($exportData, $title);

        $fileName = md5(auth()->user()->id.time()).'.xlsx';
        Excel::store(new ExcelExport($exportData), $fileName, 'excel');

        $result['file_name'] = $fileName;

        return $result;
    }

    /*
     * @Description: 经验交流导出
     * @Author:  Kzj
     * @Date: 2023-11-17 14:43:04
     */
    public function exportExperience($request)
    {
        $formKey = NULL;
        $termId = 0;
        $name = '';
        $status = '';
        $hostName = ''; //主持人
        $beginDate = '';
        $endDate = '';
        $joinName = '';
        $creatorName = '';
        $rangeDate = [];
        $jsonSearch = [];
        $hasSearch = false;

        $user = Auth::user();
        $roleType = $user->cur_role_type;
        // dd($roleType);

        if($request['form_key'] == 'TEARRESEARCH_EXCHANGE_TABLE') {//经验交流
            if($roleType == 1) {
                $formKey = 'EW_EXCHANGE_EXPERIENCE_TABLE';
            } else if ($roleType == 2) {
                $formKey = 'TEARRESEARCH_EXCHANGE_TABLE';
            } else {
                $formKey = [$request['form_key'], 'EW_EXCHANGE_EXPERIENCE_TABLE'];
            }
        }
        if (isset($request['search']['data_version_id'] )&& !empty($request['search']['data_version_id'])){
            $hasSearch = true;
            $termId = $request['search']['data_version_id'];
        }
        if (isset($request['search']['name']) && !empty($request['search']['name'])){
            $hasSearch = true;
            $name = $request['search']['name'];
        }
        if (isset($request['search']['status']) && !empty($request['search']['status'])){
            $hasSearch = true;
            $status = $request['search']['status'];
        }
        if (isset($request['search']['host_name']) && !empty($request['search']['host_name'])){
            $hasSearch = true;
            $hostName = $request['search']['host_name'];
        }
        if (isset($request['search']['begin_date']) && !empty($request['search']['begin_date'])){
            $hasSearch = true;
            $beginDate = $request['search']['begin_date'];
        }
        if (isset($request['search']['end_date']) && !empty($request['search']['end_date'])){
            $hasSearch = true;
            $endDate = $request['search']['end_date'];
        }
        //通过json的类型及optionId值进行查询
        if (isset($request['json_search']['pubications_publish_type'] )&& !empty($request['json_search']['pubications_publish_type'])){
            $hasSearch = true;
            $jsonSearch['pubications_publish_type'] = $request['json_search']['pubications_publish_type'];
        }
        if (isset($request['search']['level'] )&& !empty($request['search']['level'])){
            $hasSearch = true;
            $jsonSearch['level'] = $request['search']['level'];
        }
        if (isset($request['search']['course'] )&& !empty($request['search']['course'])){
            $hasSearch = true;
            $jsonSearch['course'] = $request['search']['course'];
        }
        if (isset($request['search']['grade'] )&& !empty($request['search']['grade'])){
            $hasSearch = true;
            $jsonSearch['grade'] = $request['search']['grade'];
        }
        if (isset($request['search']['class_item'] )&& !empty($request['search']['class_item'])){
            $hasSearch = true;
            $jsonSearch['class_item'] = $request['search']['class_item'];
        }
        if (isset($request['search']['category'] )&& !empty($request['search']['category'])){
            $hasSearch = true;
            $jsonSearch['category'] = $request['search']['category'];
        }
        if (isset($request['search']['reception_class'] )&& !empty($request['search']['reception_class'])){
            $hasSearch = true;
            $jsonSearch['reception_class'] = $request['search']['reception_class'];
        }
        if (isset($request['search']['date_range'] )&& !empty($request['search']['date_range'])){
            $hasSearch = true;
            $rangeDate = explode(' - ', $request['search']['date_range']);
        }
        if (isset($request['search']['join_name'] )&& !empty($request['search']['join_name'])){
            $hasSearch = true;
            $joinName = $request['search']['join_name'];
        }
        if (isset($request['search']['creator_name'] )&& !empty($request['search']['creator_name'])){
            $hasSearch = true;
            $creatorName = $request['search']['creator_name'];
        }
        if (!$hasSearch){
            $status = 'PASS';//导出的数据均为审核通过的
        }

        $user = Auth::user();
        $viewAll = $this->activityRepository->getViewPower($request['form_key']);

        $output = ActivityInfo::select('activity_info.*', 'b.status')
            ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
            ->when($formKey, function($q) use($formKey) {
                if(is_array($formKey)) {
                    $q->whereIn('activity_category', $formKey);
                } else {
                    $q->where('activity_category', $formKey);
                }
            })
            ->when($termId > 0, function($q) use($termId) {
                $q->whereHas('term', function ($query) use($termId) {
                    $query->where('activity_info.data_version_id', $termId);
                });
            })
            ->when($name, function($q) use($name) {
                $q->where('activity_info.name', 'like', '%' . $name . '%');
            })
            ->when($status, function($q) use($status) {
                $q->where('b.status', '=', $status);
            })
            ->when($beginDate, function($q) use($beginDate) {
                $q->where('activity_info.begin_date', '=', $beginDate);
            })
            ->when($endDate, function($q) use($endDate) {
                $q->where('activity_info.end_date', '=', $endDate);
            })
            ->when(!empty($jsonSearch), function($q) use($jsonSearch) {
                foreach($jsonSearch as $k => $id) {
                    $q->whereRaw(" JSON_EXTRACT(activity_info.`".$k."`,'$[0].optionId') = $id");
                }
            })
            ->when(!empty($rangeDate), function($q) use($rangeDate) {
                $q->where('activity_info.begin_date', '<=', $rangeDate[0])->where('activity_info.end_date', '>=', $rangeDate[1]);
            })
            ->when($hostName, function($q) use($hostName) {
                $q->whereHas('compereUser', function ($query) use($hostName) {
                    $query->where('nickname', 'like', '%' . $hostName . '%');
                });
            })
            ->when(isset($request['user_id']) && !empty($request['user_id']), function($q) use($request){//处理档案袋只查看自己的数据
                $q->whereHas('participants', function($query) use($request){
                    $query->where('user_id', $request['user_id']);
                });
            })
            ->when(!isset($request['user_id']), function($q) use($user, $viewAll){//处理非档案袋只查看自己的数据
                $q->when(!$viewAll && !$user->is_checker, function($qq) use($user){
                    $qq->whereHas('participants', function($query) use($user){
                        $query->where('user_id', $user->id);
                    });
                });
            })
            ->when($joinName, function($q) use($joinName) {
                $q->whereHas('joinUser', function ($query) use($joinName) {
                    $query->where('nickname', 'like', '%' . $joinName . '%');
                });
            })
            ->when($creatorName, function($q) use($creatorName) {
                $q->whereHas('creator', function ($query) use($creatorName) {
                    $query->where('nickname', 'like', '%' . $creatorName . '%');
                });
            })
            ->when(!empty($request['search']['materials_date']),function($q) use($request){
                return $q->whereRaw(" activity_info.data_version_id in ({$request['search']['materials_date']})");
            })
            ->with('term', 'forminstance', 'compere', 'participant', 'creator', 'instance:id,status')
            ->where('activity_info.deleted', 0)
            ->orderBy('activity_info.id', 'desc')
            ->get();
            //dd(DB::getQueryLog());
        // dd($output->toArray());

        $title = ['学年学期','主题名称','类别','日期','级别','学科','年级','研究课', '创建人', '状态'];
        $exportData = [];
        foreach($output as $k => $v){
            $status = '-';
            $disStatus = $v->instance ? $v['instance']['status'] : '';
            switch($disStatus) {
                case 'REVOKE': $status = '撤回'; break;
                case 'FILL_IN': $status = '未提交'; break;
                case 'REVIEW': $status = '待审核'; break;
                case 'REVIEW_FAIL': $status = '已驳回'; break;
                case 'PASS': $status = '审核通过'; break;
            }

            $activityType = '';
            switch($v->activity_category) {
                case 'EW_EXCHANGE_EXPERIENCE_TABLE': $activityType = '教育'; break;
                case 'TEARRESEARCH_EXCHANGE_TABLE': $activityType = '教学'; break;
            }

            $item = [
                'term' => $v->data_version_id ? $v->term->title : '',
                'name' => $v->name,
                'activity_type' => $activityType,
                'date' => $v->begin_date,
                'level' => !empty($v->level_array) ? $v['level_array'][0]['selectValue'] : '',
                'course' => !empty($v->course_set) ? $v['course_set'][0]['selectValue'] : '',
                'grade' => !empty($v->grade_array) ? $v['grade_array'][0]['selectValue'] : '',
                'reception' => !empty($v->reception_class_set) ? $v['reception_class_set'][0]['selectValue'] : '',
                'creator' => !empty($v->creator) ? $v->creator->nickname : '',
                'status' => $status
            ];

            $exportData[] = $item;
        }
        array_unshift($exportData, $title);

        $fileName = md5(auth()->user()->id.time()).'.xlsx';
        Excel::store(new ExcelExport($exportData), $fileName, 'excel');

        $result['file_name'] = $fileName;

        return $result;
    }

    /*
     * @Description: 课题研究导出
     * @Author:  Kzj
     * @Date: 2023-11-17 14:43:04
     */
    public function exportStudy($request)
    {
        // dd($request);
        $termId = 0;
        $name = '';
        $stage = 0;
        $status = -1;
        $oldStatus = "";
        $hostName = ''; //主持人
        $creatorName = ''; //创建人
        $rangeDate = [];
        $jsonSearch = [];
        $hasSearch = false;

        $user = Auth::user();

        $roleType = $user->cur_role_type;
        if($roleType == 1) {
            $formKey = 'EW_SUBJECT_RESEARCH_TABLE';
        } else if ($roleType == 2) {
            $formKey = 'TEARRESEARCH_SUBJECT_STUDY_TABLE';
        } else {
            if (isset($request['search']['activity_category'] )&& !empty($request['search']['activity_category'])){
                $formKey = $request['search']['activity_category'];
            } else {
                $formKey = ['EW_SUBJECT_RESEARCH_TABLE', 'TEARRESEARCH_SUBJECT_STUDY_TABLE'];
            }
        }
        if (isset($request['search']['data_version_id'] )&& !empty($request['search']['data_version_id'])){
            $hasSearch = true;
            $termId = $request['search']['data_version_id'];
        }
        if (isset($request['search']['name']) && !empty($request['search']['name'])){
            $hasSearch = true;
            $name = $request['search']['name'];
        }
        if (isset($request['search']['status'])){
            $status = !empty($request['search']['status'])?$request['search']['status']:-1;
            if($status >= 0) {
                $hasSearch = true;
            }
            switch($status) {
                case '0':
                    $oldStatus = 'FILL_IN';
                    break;
                case '1':
                    $oldStatus = 'REVIEW';
                    break;
                case '2':
                    $oldStatus = 'PASS';
                    break;
                case '3':
                    $oldStatus = 'REVIEW_FAIL';
                    break;
            }
        }

        if (isset($request['search']['host_name']) && !empty($request['search']['host_name'])){
            $hasSearch = true;
            $hostName = $request['search']['host_name'];
        }
        if (isset($request['search']['creator_name'] )&& !empty($request['search']['creator_name'])){
            $hasSearch = true;
            $creatorName = $request['search']['creator_name'];
        }
        if (isset($request['search']['stage'] )&& !empty($request['search']['stage'])){
            $hasSearch = true;
            $stage = $request['search']['stage'];
        }
        //通过json的类型及optionId值进行查询
        if (isset($request['search']['pubications_publish_type'] )&& !empty($request['search']['pubications_publish_type'])){
            $hasSearch = true;
            $jsonSearch['pubications_publish_type'] = $request['search']['pubications_publish_type'];
        }
        if (isset($request['search']['level'] )&& !empty($request['search']['level'])){
            $hasSearch = true;
            $jsonSearch['level'] = $request['search']['level'];
        }
        if (isset($request['search']['category'] )&& !empty($request['search']['category'])){
            $hasSearch = true;
            $jsonSearch['category'] = $request['search']['category'];
        }
        if (isset($request['search']['date_range'] )&& !empty($request['search']['date_range'])){
            $hasSearch = true;
            $rangeDate = explode(' - ', $request['search']['date_range']);
        }
        if (!$hasSearch){
            $status = 2;//导出的数据均为审核通过的
            $oldStatus = 'PASS';
        }
        // dd($hasSearch, $status);
        $viewAll = $this->activityRepository->getViewPower($request['form_key']);

        $output = ActivityInfo::select('activity_info.*', 'ss.status', 'b.status as old_status')
            ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
            ->leftJoin('study_stage as ss', function ($join) {
                $join->on('activity_info.id', '=', 'ss.activity_id')->on('activity_info.stage', '=', 'ss.stage');
            })
            ->leftJoin('participant as p', 'activity_info.id', '=', 'p.activity_info_id')
            ->manageable()
            ->when($termId > 0, function($q) use($termId) {
                $q->whereHas('term', function ($query) use($termId) {
                    $query->where('activity_info.data_version_id', $termId);
                });
            })
            ->when($formKey, function($q) use($formKey) {
                if(is_array($formKey)) {
                    $q->whereIn('activity_info.activity_category', $formKey);
                } else {
                    $q->where('activity_info.activity_category', $formKey);
                }
            })
            ->when($name, function($q) use($name) {
                $q->where('activity_info.name', 'like', '%' . $name . '%');
            })
            ->when($status >= 0, function($q) use($status, $oldStatus) {
                $q->where(function($query) use($status, $oldStatus) {
                    $query->where('ss.status', '=', $status)->orWhere('b.status', '=', $oldStatus);
                });
            })
            ->when($stage > 0, function($query) use($stage) {
                $query->where('activity_info.stage', '=', $stage);
            })
            ->when(!empty($jsonSearch), function($q) use($jsonSearch) {
                foreach($jsonSearch as $k => $id) {
                    $q->whereRaw(" JSON_EXTRACT(activity_info.`".$k."`,'$[0].optionId') = $id");
                }
            })
            ->when(!empty($rangeDate), function($q) use($rangeDate) {
                $q->where('activity_info.begin_date', '<=', $rangeDate[0])->where('activity_info.end_date', '>=', $rangeDate[1]);
            })
            ->when($hostName, function($q) use($hostName) {
                $q->whereHas('compereUser', function ($query) use($hostName) {
                    $query->where('nickname', 'like', '%' . $hostName . '%');
                });
            })
            ->when(isset($request['user_id']) && !empty($request['user_id']), function($q) use($request){//处理档案袋只查看自己的数据
                $q->whereHas('participants', function($query) use($request){
                    $query->where('user_id', $request['user_id']);
                });
            })
            ->when(!isset($request['user_id']) && !$viewAll && !$user->is_checker, function($q) use($user){//处理非档案袋只查看自己的数据
                $q->whereHas('participants', function($query) use($user){
                    $query->where('user_id', $user->id);
                });
            })
            ->when($creatorName, function($q) use($creatorName) {
                $q->whereHas('creator', function ($query) use($creatorName) {
                    $query->where('nickname', 'like', '%' . $creatorName . '%');
                });
            })
            ->when(!empty($request['search']['materials_date']),function($q) use($request){
                return $q->whereRaw(" activity_info.data_version_id in ({$request['search']['materials_date']})");
            })
            ->with('term', 'forminstance', 'compere', 'participant', 'creator', 'stages', 'instance:id,status')
            ->where('activity_info.deleted', 0)
            ->groupBy('activity_info.id')
            ->orderBy('activity_info.id', 'desc')
            ->get();
           // dd(DB::getQueryLog());
        $title = ['学年学期','课题名称','类别','开始日期','结束日期','级别','主持人','课题阶段', '创建人', '状态'];
        $exportData = [];
        foreach($output as $k => $v){
            $status = '-';
            if($v->stage <= 0) {
                $disStatus = $v->instance ? $v['instance']['status'] : '';
                switch($disStatus) {
                    case 'REVOKE': $status = '撤回'; break;
                    case 'FILL_IN': $status = '未提交'; break;
                    case 'REVIEW': $status = '待审核2'; break;
                    case 'REVIEW_FAIL': $status = '已驳回'; break;
                    case 'PASS': $status = '审核通过'; break;
                }
            } else {
                // 0-待提交，1-待审核，2-通过，3-已驳回
                switch($v->status) {
                    case 1: $status = '待审核1'; break;
                    case 2: $status = '审核通过'; break;
                    case 3: $status = '已驳回'; break;
                    default: $status = '待提交'; break;
                }
            }

            $activityType = '';
            switch($v->activity_category) {
                case 'EW_SUBJECT_RESEARCH_TABLE': $activityType = '教育'; break;
                case 'TEARRESEARCH_SUBJECT_STUDY_TABLE': $activityType = '教学'; break;
            }

            $stage = '-';
            switch($v->stage) {
                case 1: $stage = '申报阶段'; break;
                case 2: $stage = '立项阶段'; break;
                case 3: $stage = '中期阶段'; break;
                case 4: $stage = '结项阶段'; break;
            }

            $compere = !empty($v->compere) ? collect($v['compere'])->first() : '';
            $item = [
                'term' => $v->data_version_id ? $v->term->title : '',
                'name' => $v->name ? $v->name : '',
                'activity_type' => $activityType,
                'begin_date' => $v->begin_date,
                'end_date' => $v->end_date,
                'level' => !empty($v->level_array) ? collect($v['level_array'])->pluck('selectValue')->implode(',') : '',
                'compere' => $compere ? $compere['user']['nickname'] : '',
                'stage' => $stage,
                'creator' => !empty($v->creator) ? $v->creator->nickname : '',
                'status' => $status
            ];

            $exportData[] = $item;
        }
        array_unshift($exportData, $title);

        $fileName = md5(auth()->user()->id.time()).'.xlsx';
        Excel::store(new ExcelExport($exportData), $fileName, 'excel');

        $result['file_name'] = $fileName;

        return $result;
    }

    /*
     * @Description: 论文发表导出
     * @Author:  Kzj
     * @Date: 2023-11-17 14:43:04
     */
    public function exportPaper($request)
    {
        $formKey = NULL;
        $termId = 0;
        $name = '';
        $status = '';
        $hostName = ''; //主持人
        $beginDate = '';
        $endDate = '';
        $joinName = '';
        $creatorName = '';
        $rangeDate = [];
        $jsonSearch = [];
        $hasSearch = false;

        $user = Auth::user();
        $roleType = $user->cur_role_type;

        if($request['form_key'] == 'TEARRESEARCH_PUBLISH_TABLE') {//论文论著发表
            if($roleType == 1) {
                $formKey = 'EW_PAPER_PUBLISH_TABLE';
            } else if ($roleType == 2) {
                $formKey = 'TEARRESEARCH_PUBLISH_TABLE';
            } else {
                $formKey = [$request['form_key'], 'EW_PAPER_PUBLISH_TABLE'];
            }
        }
        if (isset($request['search']['data_version_id'] )&& !empty($request['search']['data_version_id'])){
            $hasSearch = true;
            $termId = $request['search']['data_version_id'];
        }
        if (isset($request['search']['name']) && !empty($request['search']['name'])){
            $hasSearch = true;
            $name = $request['search']['name'];
        }
        if (isset($request['search']['status']) && !empty($request['search']['status'])){
            $hasSearch = true;
            $status = $request['search']['status'];
        }
        if (isset($request['search']['host_name']) && !empty($request['search']['host_name'])){
            $hasSearch = true;
            $hostName = $request['search']['host_name'];
        }
        if (isset($request['search']['begin_date']) && !empty($request['search']['begin_date'])){
            $hasSearch = true;
            $beginDate = $request['search']['begin_date'];
        }
        if (isset($request['search']['end_date']) && !empty($request['search']['end_date'])){
            $hasSearch = true;
            $endDate = $request['search']['end_date'];
        }
        //通过json的类型及optionId值进行查询
        if (isset($request['json_search']['pubications_publish_type'] )&& !empty($request['json_search']['pubications_publish_type'])){
            $jsonSearch['pubications_publish_type'] = $request['json_search']['pubications_publish_type'];
        }
        if (isset($request['search']['level'] )&& !empty($request['search']['level'])){
            $hasSearch = true;
            $jsonSearch['level'] = $request['search']['level'];
        }
        if (isset($request['search']['course'] )&& !empty($request['search']['course'])){
            $hasSearch = true;
            $jsonSearch['course'] = $request['search']['course'];
        }
        if (isset($request['search']['grade'] )&& !empty($request['search']['grade'])){
            $hasSearch = true;
            $jsonSearch['grade'] = $request['search']['grade'];
        }
        if (isset($request['search']['class_item'] )&& !empty($request['search']['class_item'])){
            $hasSearch = true;
            $jsonSearch['class_item'] = $request['search']['class_item'];
        }
        if (isset($request['search']['category'] )&& !empty($request['search']['category'])){
            $hasSearch = true;
            $jsonSearch['category'] = $request['search']['category'];
        }
        if (isset($request['search']['reception_class'] )&& !empty($request['search']['reception_class'])){
            $hasSearch = true;
            $jsonSearch['reception_class'] = $request['search']['reception_class'];
        }
        if (isset($request['search']['date_range'] )&& !empty($request['search']['date_range'])){
            $hasSearch = true;
            $rangeDate = explode(' - ', $request['search']['date_range']);
        }
        if (isset($request['search']['join_name'] )&& !empty($request['search']['join_name'])){
            $hasSearch = true;
            $joinName = $request['search']['join_name'];
        }
        if (isset($request['search']['creator_name'] )&& !empty($request['search']['creator_name'])){
            $hasSearch = true;
            $creatorName = $request['search']['creator_name'];
        }
        if (!$hasSearch){
            $status = 'PASS';//导出的数据均为审核通过的
        }

        $user = Auth::user();
        $viewAll = $this->activityRepository->getViewPower($request['form_key']);

        $output = ActivityInfo::select('activity_info.*', 'b.status')
            ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
            ->when($formKey, function($q) use($formKey) {
                if(is_array($formKey)) {
                    $q->whereIn('activity_category', $formKey);
                } else {
                    $q->where('activity_category', $formKey);
                }
            })
            ->when($termId > 0, function($q) use($termId) {
                $q->whereHas('term', function ($query) use($termId) {
                    $query->where('activity_info.data_version_id', $termId);
                });
            })
            ->when($name, function($q) use($name) {
                $q->where('activity_info.name', 'like', '%' . $name . '%');
            })
            ->when($status, function($q) use($status) {
                $q->where('b.status', '=', $status);
            })
            ->when($beginDate, function($q) use($beginDate) {
                $q->where('activity_info.begin_date', '=', $beginDate);
            })
            ->when($endDate, function($q) use($endDate) {
                $q->where('activity_info.end_date', '=', $endDate);
            })
            ->when(!empty($jsonSearch), function($q) use($jsonSearch) {
                foreach($jsonSearch as $k => $id) {
                    $q->whereRaw(" JSON_EXTRACT(activity_info.`".$k."`,'$[0].optionId') = $id");
                }
            })
            ->when(!empty($rangeDate), function($q) use($rangeDate) {
                $q->where('activity_info.begin_date', '<=', $rangeDate[0])->where('activity_info.end_date', '>=', $rangeDate[1]);
            })
            ->when($hostName, function($q) use($hostName) {
                $q->whereHas('compereUser', function ($query) use($hostName) {
                    $query->where('nickname', 'like', '%' . $hostName . '%');
                });
            })
            ->when(isset($request['user_id']) && !empty($request['user_id']), function($q) use($request){//处理档案袋只查看自己的数据
                $q->whereHas('participants', function($query) use($request){
                    $query->where('user_id', $request['user_id']);
                });
            })
            ->when(!isset($request['user_id']), function($q) use($user, $viewAll){//处理非档案袋只查看自己的数据
                $q->when(!$viewAll && !$user->is_checker, function($qq) use($user){
                    $qq->whereHas('participants', function($query) use($user){
                        $query->where('user_id', $user->id);
                    });
                });
            })
            ->when($joinName, function($q) use($joinName) {
                $q->whereHas('joinUser', function ($query) use($joinName) {
                    $query->where('nickname', 'like', '%' . $joinName . '%');
                });
            })
            ->when($creatorName, function($q) use($creatorName) {
                $q->whereHas('creator', function ($query) use($creatorName) {
                    $query->where('nickname', 'like', '%' . $creatorName . '%');
                });
            })
            ->when(!empty($request['search']['materials_date']),function($q) use($request){
                return $q->whereRaw(" activity_info.data_version_id in ({$request['search']['materials_date']})");
            })
            ->with('term', 'forminstance', 'compere', 'participant', 'creator', 'instance:id,status')
            ->where('activity_info.deleted', 0)
            ->orderBy('activity_info.id', 'desc')
            ->get();
            //dd(DB::getQueryLog());
        // dd($output->toArray());

        $title = ['学年学期','论文名称','类别','发表日期','级别','创建人','状态'];
        $exportData = [];
        foreach($output as $k => $v){
            $status = '-';
            $disStatus = $v->instance ? $v['instance']['status'] : '';
            switch($disStatus) {
                case 'REVOKE': $status = '撤回'; break;
                case 'FILL_IN': $status = '未提交'; break;
                case 'REVIEW': $status = '待审核'; break;
                case 'REVIEW_FAIL': $status = '已驳回'; break;
                case 'PASS': $status = '审核通过'; break;
            }

            $activityType = '';
            switch($v->activity_category) {
                case 'TEARRESEARCH_PUBLISH_TABLE': $activityType = '教育'; break;
                case 'EW_PAPER_PUBLISH_TABLE': $activityType = '教学'; break;
            }

            $item = [
                'term' => $v->data_version_id ? $v->term->title : '',
                'name' => $v->name,
                'activity_type' => $activityType,
                'date' => $v->begin_date,
                'level' => !empty($v->level_array) ? $v['level_array'][0]['selectValue'] : '',
                'creator' => !empty($v->creator) ? $v->creator->nickname : '',
                'status' => $status
            ];

            $exportData[] = $item;
        }
        array_unshift($exportData, $title);

        $fileName = md5(auth()->user()->id.time()).'.xlsx';
        Excel::store(new ExcelExport($exportData), $fileName, 'excel');

        $result['file_name'] = $fileName;

        return $result;
    }


    /*
     * @Description: 论著发表导出
     * @Author:  Kzj
     * @Date: 2023-11-17 14:43:04
     */
    public function exportTreatise($request)
    {
        $formKey = NULL;
        $termId = 0;
        $name = '';
        $status = '';
        $hostName = ''; //主持人
        $beginDate = '';
        $endDate = '';
        $joinName = '';
        $creatorName = '';
        $rangeDate = [];
        $jsonSearch = [];
        $hasSearch = false;

        $user = Auth::user();
        $roleType = $user->cur_role_type;

        if($request['form_key'] == 'TEARRESEARCH_PUBLISH_TABLE') {//论文论著发表
            if($roleType == 1) {
                $formKey = 'EW_PAPER_PUBLISH_TABLE';
            } else if ($roleType == 2) {
                $formKey = 'TEARRESEARCH_PUBLISH_TABLE';
            } else {
                $formKey = [$request['form_key'], 'EW_PAPER_PUBLISH_TABLE'];
            }
        }
        if (isset($request['search']['data_version_id'] )&& !empty($request['search']['data_version_id'])){
            $hasSearch = true;
            $termId = $request['search']['data_version_id'];
        }
        if (isset($request['search']['name']) && !empty($request['search']['name'])){
            $hasSearch = true;
            $name = $request['search']['name'];
        }
        if (isset($request['search']['status']) && !empty($request['search']['status'])){
            $hasSearch = true;
            $status = $request['search']['status'];
        }
        if (isset($request['search']['host_name']) && !empty($request['search']['host_name'])){
            $hasSearch = true;
            $hostName = $request['search']['host_name'];
        }
        if (isset($request['search']['begin_date']) && !empty($request['search']['begin_date'])){
            $hasSearch = true;
            $beginDate = $request['search']['begin_date'];
        }
        if (isset($request['search']['end_date']) && !empty($request['search']['end_date'])){
            $hasSearch = true;
            $endDate = $request['search']['end_date'];
        }
        //通过json的类型及optionId值进行查询
        if (isset($request['json_search']['pubications_publish_type'] )&& !empty($request['json_search']['pubications_publish_type'])){
            $jsonSearch['pubications_publish_type'] = $request['json_search']['pubications_publish_type'];
        }
        if (isset($request['search']['level'] )&& !empty($request['search']['level'])){
            $hasSearch = true;
            $jsonSearch['level'] = $request['search']['level'];
        }
        if (isset($request['search']['course'] )&& !empty($request['search']['course'])){
            $hasSearch = true;
            $jsonSearch['course'] = $request['search']['course'];
        }
        if (isset($request['search']['grade'] )&& !empty($request['search']['grade'])){
            $hasSearch = true;
            $jsonSearch['grade'] = $request['search']['grade'];
        }
        if (isset($request['search']['class_item'] )&& !empty($request['search']['class_item'])){
            $hasSearch = true;
            $jsonSearch['class_item'] = $request['search']['class_item'];
        }
        if (isset($request['search']['category'] )&& !empty($request['search']['category'])){
            $hasSearch = true;
            $jsonSearch['category'] = $request['search']['category'];
        }
        if (isset($request['search']['reception_class'] )&& !empty($request['search']['reception_class'])){
            $hasSearch = true;
            $jsonSearch['reception_class'] = $request['search']['reception_class'];
        }
        if (isset($request['search']['date_range'] )&& !empty($request['search']['date_range'])){
            $hasSearch = true;
            $rangeDate = explode(' - ', $request['search']['date_range']);
        }
        if (isset($request['search']['join_name'] )&& !empty($request['search']['join_name'])){
            $hasSearch = true;
            $joinName = $request['search']['join_name'];
        }
        if (isset($request['search']['creator_name'] )&& !empty($request['search']['creator_name'])){
            $hasSearch = true;
            $creatorName = $request['search']['creator_name'];
        }
        if (!$hasSearch){
            $status = 'PASS';//导出的数据均为审核通过的
        }

        $user = Auth::user();
        $viewAll = $this->activityRepository->getViewPower($request['form_key']);

        $output = ActivityInfo::select('activity_info.*', 'b.status')
            ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
            ->when($formKey, function($q) use($formKey) {
                if(is_array($formKey)) {
                    $q->whereIn('activity_category', $formKey);
                } else {
                    $q->where('activity_category', $formKey);
                }
            })
            ->when($termId > 0, function($q) use($termId) {
                $q->whereHas('term', function ($query) use($termId) {
                    $query->where('activity_info.data_version_id', $termId);
                });
            })
            ->when($name, function($q) use($name) {
                $q->where('activity_info.name', 'like', '%' . $name . '%');
            })
            ->when($status, function($q) use($status) {
                $q->where('b.status', '=', $status);
            })
            ->when($beginDate, function($q) use($beginDate) {
                $q->where('activity_info.begin_date', '=', $beginDate);
            })
            ->when($endDate, function($q) use($endDate) {
                $q->where('activity_info.end_date', '=', $endDate);
            })
            ->when(!empty($jsonSearch), function($q) use($jsonSearch) {
                foreach($jsonSearch as $k => $id) {
                    $q->whereRaw(" JSON_EXTRACT(activity_info.`".$k."`,'$[0].optionId') = $id");
                }
            })
            ->when(!empty($rangeDate), function($q) use($rangeDate) {
                $q->where('activity_info.begin_date', '<=', $rangeDate[0])->where('activity_info.end_date', '>=', $rangeDate[1]);
            })
            ->when($hostName, function($q) use($hostName) {
                $q->whereHas('compereUser', function ($query) use($hostName) {
                    $query->where('nickname', 'like', '%' . $hostName . '%');
                });
            })
            ->when(isset($request['user_id']) && !empty($request['user_id']), function($q) use($request){//处理档案袋只查看自己的数据
                $q->whereHas('participants', function($query) use($request){
                    $query->where('user_id', $request['user_id']);
                });
            })
            ->when(!isset($request['user_id']), function($q) use($user, $viewAll){//处理非档案袋只查看自己的数据
                $q->when(!$viewAll && !$user->is_checker, function($qq) use($user){
                    $qq->whereHas('participants', function($query) use($user){
                        $query->where('user_id', $user->id);
                    });
                });
            })
            ->when($joinName, function($q) use($joinName) {
                $q->whereHas('joinUser', function ($query) use($joinName) {
                    $query->where('nickname', 'like', '%' . $joinName . '%');
                });
            })
            ->when($creatorName, function($q) use($creatorName) {
                $q->whereHas('creator', function ($query) use($creatorName) {
                    $query->where('nickname', 'like', '%' . $creatorName . '%');
                });
            })
            ->when(!empty($request['search']['materials_date']),function($q) use($request){
                return $q->whereRaw(" activity_info.data_version_id in ({$request['search']['materials_date']})");
            })
            ->with('term', 'forminstance', 'compere', 'participant', 'creator', 'instance:id,status')
            ->where('activity_info.deleted', 0)
            ->orderBy('activity_info.id', 'desc')
            ->get();
            //dd(DB::getQueryLog());
        // dd($output->toArray());

        $title = ['学年学期','论著名称','类别','发表日期','级别','创建人','状态'];
        $exportData = [];
        foreach($output as $k => $v){
            $status = '-';
            $disStatus = $v->instance ? $v['instance']['status'] : '';
            switch($disStatus) {
                case 'REVOKE': $status = '撤回'; break;
                case 'FILL_IN': $status = '未提交'; break;
                case 'REVIEW': $status = '待审核'; break;
                case 'REVIEW_FAIL': $status = '已驳回'; break;
                case 'PASS': $status = '审核通过'; break;
            }

            $activityType = '';
            switch($v->activity_category) {
                case 'TEARRESEARCH_PUBLISH_TABLE': $activityType = '教育'; break;
                case 'EW_PAPER_PUBLISH_TABLE': $activityType = '教学'; break;
            }

            $item = [
                'term' => $v->data_version_id ? $v->term->title : '',
                'name' => $v->name,
                'activity_type' => $activityType,
                'date' => $v->begin_date,
                'level' => !empty($v->level_array) ? $v['level_array'][0]['selectValue'] : '',
                'creator' => !empty($v->creator) ? $v->creator->nickname : '',
                'status' => $status
            ];

            $exportData[] = $item;
        }
        array_unshift($exportData, $title);

        $fileName = md5(auth()->user()->id.time()).'.xlsx';
        Excel::store(new ExcelExport($exportData), $fileName, 'excel');

        $result['file_name'] = $fileName;

        return $result;
    }


    /*
     * @Description: 开发科目导出
     * @Author:  Kzj
     * @Date: 2023-11-17 14:43:04
     */
    public function exportSubject($request)
    {
        $formKey = NULL;
        $termId = 0;
        $name = '';
        $status = '';
        $hostName = ''; //主持人
        $beginDate = '';
        $endDate = '';
        $joinName = '';
        $creatorName = '';
        $rangeDate = [];
        $jsonSearch = [];
        $hasSearch = false;

        $user = Auth::user();
        $roleType = $user->cur_role_type;

        $formKey = $request['form_key'] ?? '';
        if (isset($request['search']['data_version_id'] )&& !empty($request['search']['data_version_id'])){
            $hasSearch = true;
            $termId = $request['search']['data_version_id'];
        }
        if (isset($request['search']['name']) && !empty($request['search']['name'])){
            $hasSearch = true;
            $name = $request['search']['name'];
        }
        if (isset($request['search']['status']) && !empty($request['search']['status'])){
            $hasSearch = true;
            $status = $request['search']['status'];
        }
        if (isset($request['search']['host_name']) && !empty($request['search']['host_name'])){
            $hasSearch = true;
            $hostName = $request['search']['host_name'];
        }
        if (isset($request['search']['begin_date']) && !empty($request['search']['begin_date'])){
            $hasSearch = true;
            $beginDate = $request['search']['begin_date'];
        }
        if (isset($request['search']['end_date']) && !empty($request['search']['end_date'])){
            $hasSearch = true;
            $endDate = $request['search']['end_date'];
        }
        //通过json的类型及optionId值进行查询
        if (isset($request['json_search']['pubications_publish_type'] )&& !empty($request['json_search']['pubications_publish_type'])){
            $hasSearch = true;
            $jsonSearch['pubications_publish_type'] = $request['json_search']['pubications_publish_type'];
        }
        if (isset($request['search']['level'] )&& !empty($request['search']['level'])){
            $hasSearch = true;
            $jsonSearch['level'] = $request['search']['level'];
        }
        if (isset($request['search']['course'] )&& !empty($request['search']['course'])){
            $hasSearch = true;
            $jsonSearch['course'] = $request['search']['course'];
        }
        if (isset($request['search']['grade'] )&& !empty($request['search']['grade'])){
            $hasSearch = true;
            $jsonSearch['grade'] = $request['search']['grade'];
        }
        if (isset($request['search']['class_item'] )&& !empty($request['search']['class_item'])){
            $hasSearch = true;
            $jsonSearch['class_item'] = $request['search']['class_item'];
        }
        if (isset($request['search']['category'] )&& !empty($request['search']['category'])){
            $hasSearch = true;
            $jsonSearch['category'] = $request['search']['category'];
        }
        if (isset($request['search']['reception_class'] )&& !empty($request['search']['reception_class'])){
            $hasSearch = true;
            $jsonSearch['reception_class'] = $request['search']['reception_class'];
        }
        if (isset($request['search']['date_range'] )&& !empty($request['search']['date_range'])){
            $hasSearch = true;
            $rangeDate = explode(' - ', $request['search']['date_range']);
        }
        if (isset($request['search']['join_name'] )&& !empty($request['search']['join_name'])){
            $hasSearch = true;
            $joinName = $request['search']['join_name'];
        }
        if (isset($request['search']['creator_name'] )&& !empty($request['search']['creator_name'])){
            $hasSearch = true;
            $creatorName = $request['search']['creator_name'];
        }
        if (!$hasSearch){
            $status = 'PASS';//导出的数据均为审核通过的
        }

        $user = Auth::user();
        $viewAll = $this->activityRepository->getViewPower($request['form_key']);

        $output = ActivityInfo::select('activity_info.*', 'b.status')
            ->leftJoin('form_instance as b' ,'activity_info.form_instance_id','=','b.id')
            ->when($formKey, function($q) use($formKey) {
                if(is_array($formKey)) {
                    $q->whereIn('activity_category', $formKey);
                } else {
                    $q->where('activity_category', $formKey);
                }
            })
            ->when($termId > 0, function($q) use($termId) {
                $q->whereHas('term', function ($query) use($termId) {
                    $query->where('activity_info.data_version_id', $termId);
                });
            })
            ->when($name, function($q) use($name) {
                $q->where('activity_info.name', 'like', '%' . $name . '%');
            })
            ->when($status, function($q) use($status) {
                $q->where('b.status', '=', $status);
            })
            ->when($beginDate, function($q) use($beginDate) {
                $q->where('activity_info.begin_date', '=', $beginDate);
            })
            ->when($endDate, function($q) use($endDate) {
                $q->where('activity_info.end_date', '=', $endDate);
            })
            ->when(!empty($jsonSearch), function($q) use($jsonSearch) {
                foreach($jsonSearch as $k => $id) {
                    $q->whereRaw(" JSON_EXTRACT(activity_info.`".$k."`,'$[0].optionId') = $id");
                }
            })
            ->when(!empty($rangeDate), function($q) use($rangeDate) {
                $q->where('activity_info.begin_date', '<=', $rangeDate[0])->where('activity_info.end_date', '>=', $rangeDate[1]);
            })
            ->when($hostName, function($q) use($hostName) {
                $q->whereHas('compereUser', function ($query) use($hostName) {
                    $query->where('nickname', 'like', '%' . $hostName . '%');
                });
            })
            ->when(isset($request['user_id']) && !empty($request['user_id']), function($q) use($request){//处理档案袋只查看自己的数据
                $q->whereHas('participants', function($query) use($request){
                    $query->where('user_id', $request['user_id']);
                });
            })
            ->when(!isset($request['user_id']), function($q) use($user, $viewAll){//处理非档案袋只查看自己的数据
                $q->when(!$viewAll && !$user->is_checker, function($qq) use($user){
                    $qq->whereHas('participants', function($query) use($user){
                        $query->where('user_id', $user->id);
                    });
                });
            })
            ->when($joinName, function($q) use($joinName) {
                $q->whereHas('joinUser', function ($query) use($joinName) {
                    $query->where('nickname', 'like', '%' . $joinName . '%');
                });
            })
            ->when($creatorName, function($q) use($creatorName) {
                $q->whereHas('creator', function ($query) use($creatorName) {
                    $query->where('nickname', 'like', '%' . $creatorName . '%');
                });
            })
            ->when(!empty($request['search']['materials_date']),function($q) use($request){
                return $q->whereRaw(" activity_info.data_version_id in ({$request['search']['materials_date']})");
            })
            ->with('term', 'forminstance', 'compere', 'participant', 'creator', 'instance:id,status')
            ->where('activity_info.deleted', 0)
            ->orderBy('activity_info.id', 'desc')
            ->get();
            //dd(DB::getQueryLog());

        $title = ['学年学期','科目名称','开始时间','结束时间','学科','年级','创建人','状态','创建时间'];
        $exportData = [];
        foreach($output as $k => $v){
            $status = '-';
            $disStatus = $v->instance ? $v['instance']['status'] : '';
            switch($disStatus) {
                case 'REVOKE': $status = '撤回'; break;
                case 'FILL_IN': $status = '未提交'; break;
                case 'REVIEW': $status = '待审核'; break;
                case 'REVIEW_FAIL': $status = '已驳回'; break;
                case 'PASS': $status = '审核通过'; break;
            }
            $grades = '';
            foreach ($v['grade_array'] as $row){
                $grades = $grades.$row['selectValue'].' ';
            }
            $item = [
                'term' => $v->data_version_id ? $v->term->title : '',
                'name' => $v->name,
                'begin_date' => $v->begin_date,
                'end_date' => $v->end_date,
                'course' => !empty($v->course_set) ? $v['course_set'][0]['selectValue'] : '',
                'grade' => !empty($grades) ? $grades : '',
                'creator' => !empty($v->creator) ? $v->creator->nickname : '',
                'status' => $status,
                'created_at' => $v->created_at
            ];

            $exportData[] = $item;
        }

        array_unshift($exportData, $title);

        $fileName = md5(auth()->user()->id.time()).'.xlsx';
        Excel::store(new ExcelExport($exportData), $fileName, 'excel');

        $result['file_name'] = $fileName;

        return $result;
    }

    /**
     * @Description: 导出证明单
     * @type 1.公开教学 2.经验交流
     * @Author: qiankun
     * @datetime: 2025-06-06
     */
    public function exportOpenZip($request)
    {
        $user = Auth::user();
        if(!$request['ids']) {
            throw new \Exception('ID参数缺失！');
        }

        $activity = ActivityInfo::select('id', 'name','proof_material')
            ->whereIn('id',explode(',',$request['ids']))
            ->get();
        if(!$activity) {
            throw new \Exception('活动不存在！');
        }
        //dd($activity->toArray());

        $zmdFiles = [];
        foreach ($activity as $item) {
            //dd($item->proof_material_array);
            if ($item->proof_material_array) {
                foreach ($item->proof_material_array as $v){
                    if($v['field'] == 'zmd') {
                        $v['name'] = $item->name;
                        $zmdFiles[] = $v;
                    }
                }
            }
        }
        $zipPath = public_path().'/download/zip'; //zip路径
        if(!empty($request['type']) && $request['type'] == 2){
            $fileName = '经验交流_' . $activity[0]->name . '_' . date('Ymd') .rand(1,100000). '.zip';
        }else {
            $fileName = '公开教学_' . $activity[0]->name . '_' . date('Ymd').rand(1,100000) . '.zip';
        }
        $zipFileName = $zipPath.'/'.$fileName; //zip名称加路径
        $zip = new \ZipArchive();
        if($zip->open($zipFileName, \ZipArchive::CREATE | \ZipArchive::OVERWRITE) === true){
            if($zmdFiles) {
                foreach($zmdFiles as $res) {
                    $filePath = public_path() . $res['file_info']['file_dir'] . $res['file_info']['file_path'];//文件真实存在路径
                    $zip->addFile($filePath, $res['name'].'_'.$res['file_info']['file_name']);//压缩文件存储路径
                }
            }
        }
        @$zip->close();
        //var_dump($zmdFiles);exit();
        if(file_exists($zipFileName)){
            return ['file_name' => $fileName];
        }else{
            throw new \Exception('没有上传文件');
        }
    }
}
